package com.dream.xiaobo.dao.impl;

import com.dream.xiaobo.dao.AddressDao;
import com.dream.xiaobo.entity.Address;
import com.dream.xiaobo.utils.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @author xiaobo
 * @date 2021/9/2 - 8:10
 */
public class AddressDaoImpl implements AddressDao {

    //调用QueryRunner实例化对象
    private QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());

    /**
     * 根据用户Id查询用户地址信息接口实现方法
     * @param uId
     * @return
     * @throws SQLException
     */
    @Override
    public List<Address> selectAddressByUId(Integer uId) throws SQLException {

        //根据用户Id查询用户地址信息SQL
        String sql = "SELECT a_id AS aId,a_name AS aName,a_phone AS aPhone," +
                "a_detail AS aDetail,` a_state` AS aState,u_id AS uId " +
                "FROM address WHERE u_id = ? ORDER BY ` a_state` DESC";

        //执行SQL
        List<Address> list = queryRunner.query(sql, new BeanListHandler<Address>(Address.class), uId);

        //返回查询结果
        return list;
    }

    /**
     * 插入地址信息接口实现方法
     * @param address
     * @throws SQLException
     */
    @Override
    public void insertAddress(Address address) throws SQLException {

        //插入地址信息SQL
        String sql = "INSERT INTO address(a_name,a_phone,a_detail,` a_state`,u_id) VALUES(?,?,?,?,?)";

        //执行SQL
        queryRunner.update(sql, address.getaName(),address.getaPhone(),address.getaDetail(),
                address.getaState(),address.getuId());

    }

    /**
     * 根据地址Id删除地址信息接口实现方法
     * @param aId
     * @throws SQLException
     */
    @Override
    public void deleteAddressByAId(String aId) throws SQLException {

        //根据地址Id删除地址信息SQL
        String sql = "DELETE FROM address WHERE a_id = ?";

        //执行SQL
        queryRunner.update(sql,aId);

    }

    /**
     * 根据地址Id更新地址信息接口实现方法
     * @param aId
     * @throws SQLException
     */
    @Override
    public void updateAddressToByAId(String aId) throws SQLException {

        //根据地址Id更新地址信息SQL
        String sql = "UPDATE address SET ` a_state` = 1 WHERE a_id = ?";

        //执行SQL
        queryRunner.update(sql,aId);

    }

    /**
     * 根据地址Id更新地址信息接口实现方法
     * @param aId
     * @throws SQLException
     */
    @Override
    public void updateAddressNOTByAId(String aId) throws SQLException {

        //根据地址Id更新地址SQL
        String sql = "UPDATE address SET ` a_state` = 0 WHERE a_id != ?";

        //执行SQL
        queryRunner.update(sql,aId);

    }

    /**
     * 更新地址信息接口实现方法
     * @param address
     * @throws SQLException
     */
    @Override
    public void updateAddress(Address address) throws SQLException {

        //更新地址信息SQL
        String sql = "UPDATE address SET a_name = ?,a_phone = ?,a_detail = ?,` a_state`  = ? WHERE a_id = ?";

        //执行SQL
        queryRunner.update(sql,address.getaName(),address.getaPhone(),address.getaDetail(),address.getaState(),address.getaId());
    }
}
